<!doctype html>
<html lang="en-us">
<head>

    <meta charset="utf-8">
    <meta name="generator" content="Hugo 0.59.1" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <title>使用阿里云EasyExcel实现后端对Excel的读写操作 | 林贤钦</title>
    <meta property="og:title" content="使用阿里云EasyExcel实现后端对Excel的读写操作 - 林贤钦">
    <meta property="og:type" content="article">
        
    <meta property="article:published_time" content="2020-04-14T00:44:35&#43;08:00">
        
        
    <meta property="article:modified_time" content="2020-04-14T00:44:35&#43;08:00">
        
    <meta name="Keywords" content="java,博客,项目管理,软件架构,公众号,小程序">
    <meta name="description" content="使用阿里云EasyExcel实现后端对Excel的读写操作">
        
    <meta name="author" content="林贤钦">
    <meta property="og:url" content="http://linxianqin.gitee.io/post/%E4%BD%BF%E7%94%A8%E9%98%BF%E9%87%8C%E4%BA%91EasyExcel%E5%AE%9E%E7%8E%B0%E5%90%8E%E7%AB%AF%E5%AF%B9Excel%E7%9A%84%E8%AF%BB%E5%86%99%E6%93%8D%E4%BD%9C/">
    <link rel="shortcut icon" href="/img/favicon.ico" type="image/x-icon">

    <link rel="stylesheet" href="/css/normalize.css">
    
        <link rel="stylesheet" href="/css/prism.css">
    
    <link rel="stylesheet" href="/css/style.css">
    <script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

    


    
    
</head>

<body>
<header id="header" class="clearfix">
	<div class="container">
        <div class="col-group">
            <div class="site-name ">
                
                    <a id="logo" href="http://linxianqin.gitee.io">
                        林贤钦
                    </a>
                
                <p class="description">专注于Java、微信小程序、移动互联网、项目管理、软件架构</p>
            </div>
            <div>
                <nav id="nav-menu" class="clearfix">
                    <a class="current" href="http://linxianqin.gitee.io">首页</a>
                    
                    <a  href="http://linxianqin.gitee.io/archives/" title="归档">归档</a>
                    
                    <a  href="http://linxianqin.gitee.io/about/" title="关于">关于</a>
                    
                </nav>
            </div>
        </div>
    </div>
</header>


<div id="body">
        
        
    <div class="container">
        <div class="col-group">

            <div class="col-8" id="main">
                <div class="res-cons">
                    <article class="post">
                        <header>
                            <h1 class="post-title">使用阿里云EasyExcel实现后端对Excel的读写操作</h1>
                        </header>
                        <date class="post-meta meta-date">
                            2020年4月14日
                        </date>
                        
                        <div class="post-meta">
                            <span>|</span>
                            
                                <span class="meta-category"><a href="http://linxianqin.gitee.io/categories/java">java</a></span>
                            
                                <span class="meta-category"><a href="http://linxianqin.gitee.io/categories/%E9%98%BF%E9%87%8C%E4%BA%91">阿里云</a></span>
                            
                        </div>
                        
                        
                        <div class="post-meta">
                            <span id="busuanzi_container_page_pv">|<span id="busuanzi_value_page_pv"></span><span> 阅读</span></span>
                        </div>
                        
                        
                        <div class="post-content">
							<nav id="TableOfContents">
<ul>
<li><a href="#一-excel导入导出的应用场景">一、Excel导入导出的应用场景</a></li>
<li><a href="#二-easyexcel简介">二、EasyExcel简介</a></li>
<li><a href="#三-用代码实现easyexcel对excel读操作">三、用代码实现EasyExcel对Excel读操作</a>
<ul>
<li>
<ul>
<li><a href="#1-首先肯定是到maven坐标了">1、首先肯定是到maven坐标了</a></li>
<li><a href="#2-创建一个实体类-来对应excel表的内容">2、创建一个实体类，来对应excel表的内容</a></li>
<li><a href="#3-创建读取操作的监听器">3、创建读取操作的监听器</a></li>
<li><a href="#4-调用实现最终的读取">4、调用实现最终的读取</a></li>
</ul></li>
</ul></li>
<li><a href="#三-用代码实现easyexcel对excel读操作-1">三、用代码实现EasyExcel对Excel读操作</a></li>
</ul>
</nav>
                            

<h1 id="一-excel导入导出的应用场景">一、Excel导入导出的应用场景</h1>

<p>1、数据导入：减轻录入工作量</p>

<p>2、数据导出：统计信息归档</p>

<p>3、数据传输：异构系统之间数据传输</p>

<h1 id="二-easyexcel简介">二、EasyExcel简介</h1>

<ul>
<li>Java领域解析、生成Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或者JVM频繁的full gc。</li>
<li>EasyExcel是阿里巴巴开源的一个excel处理框架，<strong>以使用简单、节省内存著称</strong>。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。</li>
<li>EasyExcel采用一行一行的解析模式，并将一行的解析结果以观察者的模式通知处理（AnalysisEventListener）。</li>
</ul>

<h1 id="三-用代码实现easyexcel对excel读操作">三、用代码实现EasyExcel对Excel读操作</h1>

<p>这里举例我实际项目的例子</p>

<p>需求：课程分类的新增，用Excel的形式上传</p>

<h3 id="1-首先肯定是到maven坐标了">1、首先肯定是到maven坐标了</h3>

<pre><code class="language-xml">        &lt;!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;com.alibaba&lt;/groupId&gt;
            &lt;artifactId&gt;easyexcel&lt;/artifactId&gt;
            &lt;version&gt;2.1.1&lt;/version&gt;
        &lt;/dependency&gt;
</code></pre>

<h3 id="2-创建一个实体类-来对应excel表的内容">2、创建一个实体类，来对应excel表的内容</h3>

<pre><code class="language-java">import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class SubjectData {
    //第一列
    @ExcelProperty(index = 0)
    private String oneSubjectName;
    //第二列
    @ExcelProperty(index = 1)
    private String twoSubjectName;
}
</code></pre>

<h3 id="3-创建读取操作的监听器">3、创建读取操作的监听器</h3>

<p>注意：在这个监听器中，有几点是需要注意的，1、这个监听器，spring是没办法进行管理的，所以service层使用的时候需要把service对象也传入进来，2、集成AnalysisEventListener会实现两个方法，invoke方法读取excel内容，一行一行进行读取，我这就使用了这个方法，另外一个方法则是doAfterAllAnalysed在读写操作完之后才会执行的</p>

<pre><code class="language-java">public class SubjectExcelListener extends AnalysisEventListener&lt;SubjectData&gt; {

    //因为SubjectExcelListener不能交给spring进行管理，需要自己new，不能注入其他对象
    //不能实现数据库操作
    public EduSubjectService subjectService;
    public SubjectExcelListener() {}
    public SubjectExcelListener(EduSubjectService subjectService) {
        this.subjectService = subjectService;
    }

    //读取excel内容，一行一行进行读取
    @Override
    public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
        if(subjectData == null) {
            throw new GuliException(20001,&quot;文件数据为空&quot;);
        }

        //一行一行读取，每次读取有两个值，第一个值一级分类，第二个值二级分类
        //判断一级分类是否重复
        EduSubject existOneSubject = this.existOneSubject(subjectService, subjectData.getOneSubjectName());
        if(existOneSubject == null) { //没有相同一级分类，进行添加
            existOneSubject = new EduSubject();
            existOneSubject.setParentId(&quot;0&quot;);
            existOneSubject.setTitle(subjectData.getOneSubjectName());//一级分类名称
            subjectService.save(existOneSubject);
        }

        //获取一级分类id值
        String pid = existOneSubject.getId();

        //添加二级分类
        //判断二级分类是否重复
        EduSubject existTwoSubject = this.existTwoSubject(subjectService, subjectData.getTwoSubjectName(), pid);
        if(existTwoSubject == null) {
            existTwoSubject = new EduSubject();
            existTwoSubject.setParentId(pid);
            existTwoSubject.setTitle(subjectData.getTwoSubjectName());//二级分类名称
            subjectService.save(existTwoSubject);
        }
    }

    //判断一级分类不能重复添加
    private EduSubject existOneSubject(EduSubjectService subjectService,String name) {
        QueryWrapper&lt;EduSubject&gt; wrapper = new QueryWrapper&lt;&gt;();
        wrapper.eq(&quot;title&quot;,name);
        wrapper.eq(&quot;parent_id&quot;,&quot;0&quot;);
        EduSubject oneSubject = subjectService.getOne(wrapper);
        return oneSubject;
    }

    //判断二级分类不能重复添加
    private EduSubject existTwoSubject(EduSubjectService subjectService,String name,String pid) {
        QueryWrapper&lt;EduSubject&gt; wrapper = new QueryWrapper&lt;&gt;();
        wrapper.eq(&quot;title&quot;,name);
        wrapper.eq(&quot;parent_id&quot;,pid);
        EduSubject twoSubject = subjectService.getOne(wrapper);
        return twoSubject;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

</code></pre>

<h3 id="4-调用实现最终的读取">4、调用实现最终的读取</h3>

<pre><code class="language-java"> //添加课程分类
    @Override
    public void saveSubject(MultipartFile file,EduSubjectService subjectService) {
        try {
            //文件输入流
            InputStream in = file.getInputStream();
            //调用方法进行读取(注意要把service也传进去)
            EasyExcel.read(in, SubjectData.class,new SubjectExcelListener(subjectService)).sheet().doRead();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
</code></pre>

<h1 id="三-用代码实现easyexcel对excel读操作-1">三、用代码实现EasyExcel对Excel读操作</h1>

<p>目前还没用，这里举个简单例子</p>

<p><strong>（1）创建方法循环设置要添加到Excel的数据</strong>`</p>

<pre><code class="language-java">//循环设置要添加的数据，最终封装到list集合中
private static List&lt;DemoData&gt; data() {
    List&lt;DemoData&gt; list = new ArrayList&lt;DemoData&gt;();
    for (int i = 0; i &lt; 10; i++) {
        DemoData data = new DemoData();
        data.setSno(i);
        data.setSname(&quot;张三&quot;+i);
        list.add(data);
    }
    return list;
}
</code></pre>

<p><strong>（2）实现最终的添加操作（写法一）</strong></p>

<pre><code class="language-java">
public static void main(String[] args) throws Exception {
    // 写法1
    String fileName = &quot;F:\\11.xlsx&quot;;
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, DemoData.class).sheet(&quot;写入方法一&quot;).doWrite(data());
}
</code></pre>

<p><strong>（3）实现最终的添加操作（写法二）</strong></p>

<pre><code class="language-java">public static void main(String[] args) throws Exception {
    // 写法2，方法二需要手动关闭流
    String fileName = &quot;F:\\112.xlsx&quot;;
    // 这里 需要指定写用哪个class去写
    ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build();
    WriteSheet writeSheet = EasyExcel.writerSheet(&quot;写入方法二&quot;).build();
    excelWriter.write(data(), writeSheet);
    /// 千万别忘记finish 会帮忙关闭流
    excelWriter.finish();
}
</code></pre>

                        </div>
						 
                        
<div class="post-archive">
    <ul class="post-copyright">
        <li><strong>原文作者：</strong><a rel="author" href="http://linxianqin.gitee.io">林贤钦</a></li>
        <li style="word-break:break-all"><strong>原文链接：</strong><a href="http://linxianqin.gitee.io/post/%E4%BD%BF%E7%94%A8%E9%98%BF%E9%87%8C%E4%BA%91EasyExcel%E5%AE%9E%E7%8E%B0%E5%90%8E%E7%AB%AF%E5%AF%B9Excel%E7%9A%84%E8%AF%BB%E5%86%99%E6%93%8D%E4%BD%9C/">http://linxianqin.gitee.io/post/%E4%BD%BF%E7%94%A8%E9%98%BF%E9%87%8C%E4%BA%91EasyExcel%E5%AE%9E%E7%8E%B0%E5%90%8E%E7%AB%AF%E5%AF%B9Excel%E7%9A%84%E8%AF%BB%E5%86%99%E6%93%8D%E4%BD%9C/</a></li>
        <li><strong>版权声明：</strong>本作品采用<a rel="license" href="https://linxianqin.gitee.io">知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议</a>进行许可，非商业转载请注明出处（作者，原文链接），商业转载请联系作者获得授权。</li>
    </ul>
</div>
<br/>



                        

<div class="post-archive">
    <h2>See Also</h2>
    <ul class="listing">
        
        <li><a href="/post/%E9%98%BF%E9%87%8C%E4%BA%91oss%E5%AF%B9%E8%B1%A1%E5%AD%98%E5%82%A8%E6%9C%8D%E5%8A%A1%E5%AE%9E%E7%8E%B0/">阿里云oss对象存储服务实现</a></li>
        
        <li><a href="/post/%E6%B5%85%E8%B0%88springboot-Security-Jwt-%E5%AE%9E%E7%8E%B0token/">springBoot&#43;Security&#43;JWT&#43;Jpa&#43;Vue实现登录权限控制</a></li>
        
        <li><a href="/post/springSecurity%E7%9A%84session%E7%AE%A1%E7%90%86%E5%8F%8A%E5%AE%89%E5%85%A8%E9%85%8D%E7%BD%AE/">SpringSecurity的session管理及安全配置</a></li>
        
        <li><a href="/post/springSecurity%E8%87%AA%E5%AE%9A%E4%B9%89%E8%AE%A4%E8%AF%81%E7%BB%93%E6%9E%9C%E5%A4%84%E7%90%86/">SpringSecurity自定义认证结果处理</a></li>
        
        <li><a href="/post/springSecurity%E4%B8%ADformLogin%E7%99%BB%E5%BD%95%E8%AE%A4%E8%AF%81%E6%A8%A1%E5%BC%8F/">SpringSecurity中formLogin登录认证模式</a></li>
        
    </ul>
</div>


                        <div class="post-meta meta-tags">
                            
                            <ul class="clearfix">
                                
                                <li><a href="http://linxianqin.gitee.io/tags/%E9%98%BF%E9%87%8C%E4%BA%91">阿里云</a></li>
                                
                                <li><a href="http://linxianqin.gitee.io/tags/java">java</a></li>
                                
                            </ul>
                            
                        </div>
                    </article>
                    
    

    
    
    <div class="post bg-white">
      <script src="https://utteranc.es/client.js"
            repo= "757610938/hugo-blogs-utterances"
            issue-term="pathname"
            theme="github-light"
            crossorigin="anonymous"
            async>
      </script>
    </div>
    
                </div>
            </div>
            <div id="secondary">
    <section class="widget">
        <form id="search" action="//www.google.com/search" method="get" accept-charset="utf-8" target="_blank" _lpchecked="1">
      
      <input type="text" name="q" maxlength="20" placeholder="Search">
      <input type="hidden" name="sitesearch" value="http://linxianqin.gitee.io">
      <button type="submit" class="submit icon-search"></button>
</form>
    </section>
    
    <section class="widget">
        <h3 class="widget-title">最近文章</h3>
<ul class="widget-list">
    
    <li>
        <a href="http://linxianqin.gitee.io/post/%E9%98%9F%E5%88%97/" title="队列">队列</a>
    </li>
    
    <li>
        <a href="http://linxianqin.gitee.io/post/%E7%A8%80%E7%96%8F%E6%95%B0%E7%BB%84/" title="稀疏数组">稀疏数组</a>
    </li>
    
    <li>
        <a href="http://linxianqin.gitee.io/post/%E4%BD%BF%E7%94%A8%E9%98%BF%E9%87%8C%E4%BA%91EasyExcel%E5%AE%9E%E7%8E%B0%E5%90%8E%E7%AB%AF%E5%AF%B9Excel%E7%9A%84%E8%AF%BB%E5%86%99%E6%93%8D%E4%BD%9C/" title="使用阿里云EasyExcel实现后端对Excel的读写操作">使用阿里云EasyExcel实现后端对Excel的读写操作</a>
    </li>
    
    <li>
        <a href="http://linxianqin.gitee.io/post/%E9%98%BF%E9%87%8C%E4%BA%91oss%E5%AF%B9%E8%B1%A1%E5%AD%98%E5%82%A8%E6%9C%8D%E5%8A%A1%E5%AE%9E%E7%8E%B0/" title="阿里云oss对象存储服务实现">阿里云oss对象存储服务实现</a>
    </li>
    
    <li>
        <a href="http://linxianqin.gitee.io/post/vue.js%E5%9F%BA%E6%9C%AC%E8%AF%AD%E6%B3%95/" title="Vue">Vue</a>
    </li>
    
    <li>
        <a href="http://linxianqin.gitee.io/post/ES6%E5%85%A5%E9%97%A8/" title="ES6入门">ES6入门</a>
    </li>
    
    <li>
        <a href="http://linxianqin.gitee.io/post/%E6%B5%85%E8%B0%88springboot-Security-Jwt-%E5%AE%9E%E7%8E%B0token/" title="springBoot&#43;Security&#43;JWT&#43;Jpa&#43;Vue实现登录权限控制">springBoot&#43;Security&#43;JWT&#43;Jpa&#43;Vue实现登录权限控制</a>
    </li>
    
    <li>
        <a href="http://linxianqin.gitee.io/post/springSecurity%E7%9A%84session%E7%AE%A1%E7%90%86%E5%8F%8A%E5%AE%89%E5%85%A8%E9%85%8D%E7%BD%AE/" title="SpringSecurity的session管理及安全配置">SpringSecurity的session管理及安全配置</a>
    </li>
    
    <li>
        <a href="http://linxianqin.gitee.io/post/springSecurity%E8%87%AA%E5%AE%9A%E4%B9%89%E8%AE%A4%E8%AF%81%E7%BB%93%E6%9E%9C%E5%A4%84%E7%90%86/" title="SpringSecurity自定义认证结果处理">SpringSecurity自定义认证结果处理</a>
    </li>
    
    <li>
        <a href="http://linxianqin.gitee.io/post/springSecurity%E4%B8%ADformLogin%E7%99%BB%E5%BD%95%E8%AE%A4%E8%AF%81%E6%A8%A1%E5%BC%8F/" title="SpringSecurity中formLogin登录认证模式">SpringSecurity中formLogin登录认证模式</a>
    </li>
    
</ul>
    </section>

    

    <section class="widget">
        <h3 class="widget-title">分类</h3>
<ul class="widget-list">
    
    <li><a href="http://linxianqin.gitee.io/categories/JWT/">JWT (1)</a></li>
    
    <li><a href="http://linxianqin.gitee.io/categories/hugo/">hugo (2)</a></li>
    
    <li><a href="http://linxianqin.gitee.io/categories/java/">java (14)</a></li>
    
    <li><a href="http://linxianqin.gitee.io/categories/springSecurity/">springSecurity (4)</a></li>
    
    <li><a href="http://linxianqin.gitee.io/categories/vue/">vue (4)</a></li>
    
    <li><a href="http://linxianqin.gitee.io/categories/yml/">yml (1)</a></li>
    
    <li><a href="http://linxianqin.gitee.io/categories/%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84/">数据结构 (2)</a></li>
    
    <li><a href="http://linxianqin.gitee.io/categories/%E8%BD%AF%E4%BB%B6%E6%B5%8B%E8%AF%95/">软件测试 (1)</a></li>
    
    <li><a href="http://linxianqin.gitee.io/categories/%E9%98%BF%E9%87%8C%E4%BA%91/">阿里云 (2)</a></li>
    
</ul>
    </section>

    <section class="widget">
        <h3 class="widget-title">标签</h3>
<div class="tagcloud">
    
    <a href="http://linxianqin.gitee.io/tags/hugo/">hugo</a>
    
    <a href="http://linxianqin.gitee.io/tags/java/">java</a>
    
    <a href="http://linxianqin.gitee.io/tags/java8/">java8</a>
    
    <a href="http://linxianqin.gitee.io/tags/spring/">spring</a>
    
    <a href="http://linxianqin.gitee.io/tags/springBoot/">springBoot</a>
    
    <a href="http://linxianqin.gitee.io/tags/ssm/">ssm</a>
    
    <a href="http://linxianqin.gitee.io/tags/vue/">vue</a>
    
    <a href="http://linxianqin.gitee.io/tags/yml/">yml</a>
    
    <a href="http://linxianqin.gitee.io/tags/%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84/">数据结构</a>
    
    <a href="http://linxianqin.gitee.io/tags/%E8%BD%AF%E4%BB%B6%E6%B5%8B%E8%AF%95/">软件测试</a>
    
    <a href="http://linxianqin.gitee.io/tags/%E9%98%BF%E9%87%8C%E4%BA%91/">阿里云</a>
    
</div>
    </section>

    
<section class="widget">
    <h3 class="widget-title">友情链接</h3>
    <ul class="widget-list">
        
        <li>
            <a target="_blank" href="https://www.cnki.net" title="中国知网">中国知网</a>
        </li>
        
        <li>
            <a target="_blank" href="https://linxianqin.com" title="个人的另一个博客网站(国内加载慢)">个人的另一个博客网站(国内加载慢)</a>
        </li>
        
    </ul>
</section>


    <section class="widget">
        <h3 class="widget-title">其它</h3>
        <ul class="widget-list">
            <li><a href="http://linxianqin.gitee.io/index.xml">文章 RSS</a></li>
        </ul>
    </section>
</div>
        </div>
    </div>
</div>
<footer id="footer">
    <div class="container">
        &copy; 2020 <a href="http://linxianqin.gitee.io">林贤钦 By 林贤钦</a>.
        Powered by <a rel="nofollow noreferer noopener" href="https://gohugo.io" target="_blank">Hugo</a>.
        <a href="https://www.flysnow.org/" target="_blank">Theme</a> based on <a href="https://github.com/rujews/maupassant-hugo" target="_blank">maupassant</a>.
        
    </div>
</footer>


    
    <script type="text/javascript">
        
        (function () {
            $("pre code").parent().addClass("line-numbers")
        }());

        window.MathJax = {
            tex2jax: {
                inlineMath: [['$', '$']],
                processEscapes: true
                }
            };
    </script>
    <script type="text/javascript" src="/js/prism.js" async="true"></script>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.5/MathJax.js?config=TeX-MML-AM_CHTML' async></script>


<a id="rocket" href="#top"></a>
<script type="text/javascript" src="/js/totop.js?v=0.0.0" async=""></script>

<script type="application/javascript">
var doNotTrack = false;
if (!doNotTrack) {
	window.ga=window.ga||function(){(ga.q=ga.q||[]).push(arguments)};ga.l=+new Date;
	ga('create', 'GA ID', 'auto');
	
	ga('send', 'pageview');
}
</script>
<script async src='https://www.google-analytics.com/analytics.js'></script>



    <script type="text/javascript" src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js" async></script>




</body>
</html>
